﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;

namespace Final.App_Code
{
    public class StudentDataClass
    {
        public void createstudent(Student e)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SubjectManagementDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "Insert into Account (id,Student_Name,Password,Course_Code,Course_Name,Module,Contact,Email,Role)" +
                " VALUES (@id,@name,@password,@coursecode," +
                "@coursename,@module,@contact,@email,@Role)";
                
               
                comm.Parameters.AddWithValue("@id", e.Adminno);
                comm.Parameters.AddWithValue("@name", e.Name);
                comm.Parameters.AddWithValue("@password", e.Password);
                comm.Parameters.AddWithValue("@coursecode", e.Coursecode);
                comm.Parameters.AddWithValue("@coursename", e.Coursename);
                comm.Parameters.AddWithValue("@module", e.Module);
                comm.Parameters.AddWithValue("@contact", e.Contact);
                comm.Parameters.AddWithValue("@email", e.Email);
                comm.Parameters.AddWithValue("@Role", e.Role);
                int a = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        public void UpdateEvent(Student e)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SubjectManagementDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Account SET Id = @Id, Student_Name = @Student_Name, Password = @Password, Course_Code = @Course_Code, Course_Name = @Course_Name, Module = @Module, Contact = @Contact, Email = @Email, Role = @Role WHERE Id=@Id";
                comm.Parameters.AddWithValue("@Id", e.Adminno);
                comm.Parameters.AddWithValue("@Student_Name", e.Name);
                comm.Parameters.AddWithValue("@Password", e.Password);
                comm.Parameters.AddWithValue("@Course_Code", e.Coursecode);
                comm.Parameters.AddWithValue("@Course_Name", e.Coursename);
                comm.Parameters.AddWithValue("@Module", e.Module);
                comm.Parameters.AddWithValue("@Contact", e.Contact);
                comm.Parameters.AddWithValue("@Email", e.Email);
                comm.Parameters.AddWithValue("@Role", e.Role);
                int editstudent = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        

        public List<Student> ListAll()
        {
            List<Student> results = new List<Student>();
            SqlConnection conn = null;
            
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SubjectManagementDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "Select * from Account";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                Student c = new Student();
                c.Name = (string)dr["Student_Name"];
                c.Adminno=(string)dr["id"];
                c.Coursecode=(string)dr["Course_code"];
                c.Coursename=(string)dr["Course_name"];
                c.Module = (string)dr["Module"];
                c.Contact=(int)dr["Contact"];
                c.Email=(string)dr["Email"];
                c.Role = (string)dr["Role"];
                results.Add(c);
                }
                dr.Close();
                
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return results;
        }

        public static void UpdateModule(Student e)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SubjectManagementDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Account SET Module = @Module WHERE Id=@Id";
                comm.Parameters.AddWithValue("@Module", e.Module);
                comm.Parameters.AddWithValue("@Id", e.Adminno);
                int row = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public string RetrievePassword(string userID)
        {
            string password = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SubjectManagementDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select Password from Account where Id = @Id";
                comm.Parameters.AddWithValue("@id", userID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    password = dr["Password"].ToString();
                }
                dr.Close();
                return password;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public string RetrieveRole(string userID)
        {
            string role = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SubjectManagementDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select Role from Account where Id = @Id";
                comm.Parameters.AddWithValue("@Id", userID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    role = dr["Role"].ToString();
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return role;
        }
    }
}
